package com.codeying.dao;

import com.codeying.entity.Star;
import com.codeying.utils.CommonUtils;
import com.codeying.utils.JdbcUtils;
import com.codeying.utils.MyUtils;
import com.codeying.utils.component.QueryMap;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 收藏Dao，和数据库互动。
 */
public class StarDao extends AbstractDao<Star> {

    @Override
    protected Class<Star> clazz() {
        return Star.class;
    }

    @Override
    public String tbName() {
        return "tb_star";
    }

    public static StarDao me = new StarDao();

    /**
     * 收藏
     */
    public boolean star(String userid,String role,String itemId,String itemType){
        if(hasStar(userid,role,itemId,itemType)!=null){
            return false;
        }
        Star star = new Star();
        star.setId(CommonUtils.newId());
        star.setCreatetime(new Date());
        star.setItemid(Integer.parseInt(itemId));
        star.setItemtype(itemType);
        star.setUserid(Integer.parseInt(userid));
        star.setUserrole(role);
        insert(star);
        return true;
    }

    /**
     * is收藏
     */
    public Object hasStar(String userid, String role, String itemId, String itemType){
        List<Star> list = list(new QueryMap().add("itemid", itemId).add("userid", userid).add("userrole",role).add("itemtype",itemType));
        if(list.size()>0){
            return list.get(0).getId();
        }
        return null;
    }

    /**
     * top N的记录
     * @param type
     * @return
     */
    public List<Star> topN(String type,int n){
        String sql = "select itemid,count(1) count from tb_star where itemtype='"+type+"' group by itemid order by count desc";
        sql = JdbcUtils.getPageSql(sql,1,n);
        QueryRunner qr = new QueryRunner();
        try {
            List<Star> topN = qr.query(JdbcUtils.getConnection(), sql, new BeanListHandler<>(clazz()));
            List<Star> resList = new ArrayList<>();
            //填充内容
            for (Star star : topN) {
                sql = "select  * from tb_"+type+" where id='"+star.getItemid()+"'";
                List<Star> res = qr.query(JdbcUtils.getConnection(), sql, new BeanListHandler<>(clazz()));
                if(res==null|| res.size()==0){
                    //内容已经被删除,删除所有收藏
                    qr.update(JdbcUtils.getConnection(), "delete from tb_star where itemid='"+star.getItemid()+"'");
                }else {
                    star.setShowpic(res.get(0).getShowpic());
                    star.setShowtitle(res.get(0).getShowtitle());
                    star.setShowdesc(res.get(0).getShowdesc());
                    star.setPublishtime(res.get(0).getPublishtime());
                    resList.add(star);
                }
            }
            return resList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 我的收藏
     */
    public List<Star> userStar(String userid,String role){
        List<Star> list = list(new QueryMap().add("userid",userid).add("userrole",role));
        QueryRunner qr = new QueryRunner();
        List<Star> resList = new ArrayList<>();
        //填充内容
        for (Star star : list) {
            try {
                String sql = "select  * from tb_"+star.getItemtype()+" where id='"+star.getItemid()+"'";
                List<Star> res = qr.query(JdbcUtils.getConnection(), sql, new BeanListHandler<>(clazz()));
                if(res==null|| res.size()==0){
                    //内容已经被删除
                    deleteById(String.valueOf(star.getId()));
                }else {
                    star.setShowpic(res.get(0).getShowpic());
                    star.setShowtitle(res.get(0).getShowtitle());
                    star.setShowdesc(res.get(0).getShowdesc());
                    star.setPublishtime(res.get(0).getPublishtime());
                    resList.add(star);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return resList;
    }

    /**
     * 将map参数转换成sql的where条件语句。
     */
    @Override
    public String toConditionSql(Map<String, String> paramMap) {
        StringBuilder sqlBuilder = new StringBuilder("");//接下来拼接查询条件,比如 and xx=xx and XX < xx
        if (paramMap != null) {
            String itemid = paramMap.get("itemid");
            if (MyUtils.isNotEmpty(itemid)) {
                sqlBuilder.append(" and itemid = '").append(itemid).append("'");// 使用模糊查询
            }
            String itemtype = paramMap.get("itemtype");
            if (MyUtils.isNotEmpty(itemtype)) {
                sqlBuilder.append(" and itemtype = '").append(itemtype).append("'");// 使用模糊查询
            }
            String userid = paramMap.get("userid");
            if (MyUtils.isNotEmpty(userid)) {
                sqlBuilder.append(" and userid = '").append(userid).append("'");// 使用模糊查询
            }
            String username = paramMap.get("username");
            if (MyUtils.isNotEmpty(username)) {
                sqlBuilder.append(" and username = '").append(username).append("'");// 使用模糊查询
            }
            String userrole = paramMap.get("userrole");
            if (MyUtils.isNotEmpty(userrole)) {
                sqlBuilder.append(" and userrole = '").append(userrole).append("'");// 使用模糊查询
            }
            if (MyUtils.isNotEmpty(paramMap.get("createtimeL"))) {
                sqlBuilder.append(" and createtime >= '").append(paramMap.get("createtimeL").replace("T", " ")).append("'");
            }
            if (MyUtils.isNotEmpty(paramMap.get("createtimeR"))) {
                sqlBuilder.append(" and createtime <= '").append(paramMap.get("createtimeR").replace("T", " ")).append("'");
            }
            //排序
            String orderby = paramMap.get("orderby");
            if (orderby != null) {
                sqlBuilder.append(" order by ");
                String[] orderbys = orderby.split(",");
                for (int i = 0; i < orderbys.length; i++) {
                    String s = orderbys[i];
                    sqlBuilder.append(s);
                    if (i != orderbys.length - 1) {
                        sqlBuilder.append(",");
                    }
                }
            }
        }
        return sqlBuilder.toString();
    }

    /**
     * 插入
     */
    @Override
    public int insert(Star entity) {
        try {
            //构造sql语句
            List<Object> paramList = new ArrayList<>();
            StringBuilder reParamNames = new StringBuilder();
            StringBuilder reParams = new StringBuilder();
            paramList.add(entity.getId());
            if (entity.getItemid() != 0) {
                reParamNames.append(",itemid");
                reParams.append(",?");
                paramList.add(entity.getItemid());
            }
            if (entity.getItemtype() != null) {
                reParamNames.append(",itemtype");
                reParams.append(",?");
                paramList.add(entity.getItemtype());
            }
            if (entity.getUserid() != 0) {
                reParamNames.append(",userid");
                reParams.append(",?");
                paramList.add(entity.getUserid());
            }
            if (entity.getUserrole() != null) {
                reParamNames.append(",userrole");
                reParams.append(",?");
                paramList.add(entity.getUserrole());
            }
            if (entity.getCreatetime() != null) {
                reParamNames.append(",createtime");
                reParams.append(",?");
                paramList.add(entity.getCreatetime());
            }
            QueryRunner qr = new QueryRunner();  //创建QueryRunner类对象
            //不为空的插入
            String sqlBuilder = "INSERT INTO tb_star (id" + reParamNames.toString() + ")VALUES(?" + reParams.toString() + ")";
            log(sqlBuilder.toString());
            return qr.update(JdbcUtils.getConnection(), sqlBuilder, paramList.toArray());  // 执行SQL语句
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("StarDao -> insert(-)报错了");
        }
        return 0;
    }

    /**
     * 根据Id更新
     */
    @Override
    public int updateById(Star entity) {
        try {
            QueryRunner qr = new QueryRunner();  //创建QueryRunner类对象
            StringBuilder sqlBuilder = new StringBuilder("update tb_star set id = ? ");
            List<Object> paramList = new ArrayList<>();
            paramList.add(entity.getId());
            if (entity.getItemid() != 0) {
                sqlBuilder.append(", itemid = ? ");
                paramList.add(entity.getItemid());
            }
            if (entity.getItemtype() != null) {
                sqlBuilder.append(", itemtype = ? ");
                paramList.add(entity.getItemtype());
            }
            if (entity.getUserid() != 0) {
                sqlBuilder.append(", userid = ? ");
                paramList.add(entity.getUserid());
            }
            if (entity.getUserrole() != null) {
                sqlBuilder.append(", userrole = ? ");
                paramList.add(entity.getUserrole());
            }
            if (entity.getCreatetime() != null) {
                sqlBuilder.append(", createtime = ? ");
                paramList.add(entity.getCreatetime());
            }
            //最后拼接id条件
            sqlBuilder.append("where id = ?");
            paramList.add(entity.getId());
            log(sqlBuilder.toString(), paramList);
            return qr.update(JdbcUtils.getConnection(), sqlBuilder.toString(), paramList.toArray());  // 执行SQL语句
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("StarDao -> updateById(-)报错了");
        }
        return 0;
    }

}

